package db.jdbc;

import db.AnnouncementRepository;
import domain.Announcement;
import domain.Club;
import domain.ClubAdministrator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 公告资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcAnnouncementRepository implements AnnouncementRepository {

    private static final String INSERT_ANNOUNCEMENT = "insert into announcement(publishTime,_content) values(?,?)";
    private static final String INSERT_ANNOUNCEMENT_PUBLISHER = "insert into announcement_publisher(announcementID, clubAdminID) values(?,?)";

    private static final String DELETE_ANNOUNCEMENT = "delete from announcement where announcementID = ?";
    private static final String DELETE_ANNOUNCEMENT_PUBLISHER = "delete from announcement_publisher where announcementID = ?";

    private static final String UPDATE_ANNOUNCEMENT = "update announcement set publishTime = ?, content = ? where announcementID = ?";
    private static final String SELECT_ANNOUNCEMENT = "select * from announcement";
    private static final String SELECT_ANNOUNCEMENT_BY_PAGE = SELECT_ANNOUNCEMENT + " order by announcementID  limit ? offset  ?";
    private static JdbcTemplate jdbc;

    @Autowired
    public JdbcAnnouncementRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 获取公告的总数
     *
     * @return 公告的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(announcementID) from announcement");
    }

    /**
     * 获取特定社团中公告的总数
     *
     * @param clubID 社团ID
     * @return 社团中公告的数量
     */
    @Override
    public long countByClubID(int clubID) {
        return jdbc.queryForInt("select count(a.announcementID) from announcement as a,announcement_publisher as ap,club_clubAdministrators as cc where cc.clubID =? and cc.clubAdminID=ap.clubAdminID and ap.announcementID= a.announcementID",clubID);
    }

    /**
     * 创建一个新的公告
     *
     * @param announcement 新公告的信息
     * @return 新创建的公告
     */
    @Override
    public Announcement save(Announcement announcement) {
        ClubAdministrator publisher = announcement.getPublisher();
        Date tmpTime = new Date(System.currentTimeMillis());
        tmpTime = new Date(Math.round(tmpTime.getTime()*0.001)*1000);
        jdbc.update(INSERT_ANNOUNCEMENT, tmpTime, announcement.getContent());
        int id = jdbc.queryForInt("SELECT announcementID FROM announcement WHERE publishtime=?", tmpTime);
        jdbc.update(INSERT_ANNOUNCEMENT_PUBLISHER, id, publisher.getClubAdminID());
        return announcement;
    }

    /**
     * 根据ID查找公告
     *
     * @param announcementID@return 对应ID的公告
     */
    @Override
    public Announcement findOne(int announcementID) {
        Announcement announcement = null;
        try {
            announcement = jdbc.queryForObject(SELECT_ANNOUNCEMENT + " where announcementID=?",
                    new AnnouncementRowMapper(), announcementID);
        } catch (DataAccessException e) {
        }
        return announcement;
    }

    /**
     * 分页查找公告
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 分页的公告列表
     */
    @Override
    public PaginationSupport<Announcement> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<Announcement>(new ArrayList<Announcement>(0), 0);
        }

        List<Announcement> items = jdbc.query(SELECT_ANNOUNCEMENT_BY_PAGE, new AnnouncementRowMapper(), pageSize, startIndex);
        PaginationSupport<Announcement> ps = new PaginationSupport<Announcement>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    /**
     * 删除公告
     *
     * @param announcementID 公告ID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteAnnouncement(int announcementID) {
        return jdbc.update(DELETE_ANNOUNCEMENT_PUBLISHER, announcementID) != 0 &&
                jdbc.update(DELETE_ANNOUNCEMENT, announcementID) != 0;
    }

    /**
     * 获取所有公告
     *
     * @return 所有公告的列表
     */
    @Override
    public List<Announcement> findAll() {
        List<Announcement> announcements=jdbc.query(SELECT_ANNOUNCEMENT + " order by announcementID", new AnnouncementRowMapper());
        for(Announcement announcement:announcements){
            ClubAdministrator publisher=null;
            try{
                publisher=jdbc.queryForObject("select ca.* from clubadministrator as ca,announcement_publisher as ap where ap.announcementID=? and ca.clubAdminID=ap.clubAdminID", new JdbcClubAdministratorRepository.ClubAdministratorRowMapper(),announcement.getAnnouncementID());
            } catch (DataAccessException e) {
            }
            announcement.setPublisher(publisher);
        }
        return announcements;
    }

    /**
     * 获取社团所有公告
     *
     * @param club 社团
     * @return 社团所有公告
     */
    @Override
    public List<Announcement> findAnnouncementsByClub(Club club) {
        return jdbc.query("select a.* from announcement as a, announcement_publisher as ap, club_clubAdministrators as cc where cc.clubID=? and cc.clubAdminID=ap.clubAdminID and ap.announcementID=a.announcementID", new AnnouncementRowMapper(), club.getClubID());
    }

    private static class AnnouncementRowMapper implements RowMapper<Announcement> {
        /**
         * 将数据库查询结果映射到Announcement对象
         *
         * @param rs 查询结果集
         * @param i 行号
         * @return Announcement对象
         * @throws SQLException SQL异常
         */
        public Announcement mapRow(ResultSet rs, int i) throws SQLException {
            Announcement announcement =new Announcement();

            // 获取announcement字段，并构造Announcement对象
            announcement.setAnnouncementID(rs.getInt("announcementID"));
            announcement.setPublishTime(rs.getTimestamp("publishTime"));
            announcement.setContent(rs.getString("_content"));

            // 查询发布人信息并设置到Announcement
            ClubAdministrator publisher =jdbc.queryForObject("select ca.* from clubadministrator as ca,announcement_publisher as ap where ap.announcementID=? and ca.clubAdminID=ap.clubAdminID", new JdbcClubAdministratorRepository.ClubAdministratorRowMapper(),announcement.getAnnouncementID());
            announcement.setPublisher(publisher);

            // 返回构造的Announcement对象
            return announcement;
        }
    }
}
